from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
import openpyxl.workbook
from config import *
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures,json,os

class BTFYFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='标题翻译', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        username=get_config('Uname')
        if username=='wangzhengjie':
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
        elif username=='xuzhenxiong':
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
        elif username=='guoenping':
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
        elif username=='zhangzhiwei':
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
        elif username=='zhengyudie':
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
        else:
            BtfyYNFrame(self).pack(fill=X,padx=2,pady=2)
            BtfyPtFrame(self).pack(fill=X,padx=2,pady=2)
            BtfyThFrame(self).pack(fill=X,padx=2,pady=2)



class BtfyYNFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'当前未导入翻译表')
        self.createWidgets()
        Thread(target=self.getkwcount).start()

    def createWidgets(self):
        Label(self, text='标题翻译 - 印尼语', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        Label(f1, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01,text='模版类型:').pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.MbtypeSelect['value']=list(MBTYPES.keys())
        self.MbtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect.current(0)
        self.MbtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        Label(f01,text='货币类型:').pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.HBtypeSelect['value']=CURRENCYS
        self.HBtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect.current(0)
        self.HBtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)
        Label(f00, text='最大字符数:').pack(side=LEFT, padx=2, pady=2)
        self.WordNumIpt = Entry(f00,width=10)
        self.WordNumIpt.insert(0,4500)
        self.WordNumIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00,text='导出个数:').pack(side=LEFT, padx=2, pady=2)
        self.fileNumIpt = Entry(f00,width=10)
        self.fileNumIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_dc=Button(f00,text='导出原始标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_title).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,padx=2)
        self.btn_dr=Button(f02,text='上传印尼标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yntitle).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        time.sleep(20)
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        try:
            rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where 
                                    datatype=? and currency=? and pon=1''',(datatype,currency))
            
            rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where yntitle is not null 
                                    and datatype=? and currency=? and pon=1''',(datatype,currency))
            
            self.statustxt.set(f'需翻译标题共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
            time.sleep(10)
        except Exception as e:
            pass

    def dc_title(self):
        
        if not self.WordNumIpt.get() or not self.fileNumIpt.get():
            messagebox.showinfo('提示','请先填写字符限制和文件数量')
            return
        
        self.btn_dc.config(state=DISABLED)
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        wordnum=int(self.WordNumIpt.get())
        filenum=int(self.fileNumIpt.get())
        print(f'每份文件最大字符数:{wordnum},导出文件个数:{filenum}')
        rp=self.ms.ExecQuery(f'''select itemid,pname from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                yntitle is null and datatype=? and currency=? and pon=1 GROUP BY itemid) ''',(datatype,currency))
        current_timestamp = time.time()
        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(current_timestamp))
        dcmr=f'原始标题{current_time_str}'
        os.mkdir(dcmr)
        wordcount=0
        filecount=0
        row=0
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['PID','标题','印尼的标题'])
        for itemid,title in rp:
            try:
                if filecount>=filenum:
                    print(f'已经成功导出{filecount}份文件')
                    break
                if (wordcount+len(title))>=wordnum:
                    wb.save(f'{dcmr}/原始标题{filecount+1}.xlsx')
                    print(f'已导出{filecount+1}.xlsx')
                    wb=openpyxl.Workbook()
                    ws=wb[wb.sheetnames[0]]
                    ws.append(['PID','标题','印尼的标题'])
                    wordcount=0
                    filecount+=1
                    
                ws.append([itemid,title,''])
                wordcount+=len(title)
            except Exception as e:
                print(f'第{row}行,出错=>{e}')
            row+=1

        print(f'本次导出标题完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出印尼标题完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)
    
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yntitle(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)

        wb = openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池

            for item in tsb:
                executor.submit(self.uptask, [item['PID'],item['印尼的标题']])
        executor.shutdown(wait=True)
        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        #先生成随机码
        itemid,yntitle=item
        
        while True:
            sjm=get_random_code()
            rs= self.ms.ExecQuery('select count(*) from RandomCodes where RandomCode=?',(sjm,))
            if rs[0][0]==0:
                break
            time.sleep(1)
        yntitleRcode=f'{yntitle} |{sjm}|'
        aff=self.ms.ExecNoQuery('update ProShopeePh set yntitle=?,yntitleRcode=? where itemid=?',(yntitle,yntitleRcode,itemid))
        aff2=self.ms.ExecNoQuery('insert into RandomCodes(itemid,RandomCode) values(?,?)',(itemid,sjm))
        aff3=self.ms.ExecNoQuery('update proshopeeph_pmbid set hasyntitle=1 where itemid=?',(itemid,))
        
        if aff>0:
            self.cpcount+=1
            print(f'成功翻译{itemid},随机码:{sjm},状态:{aff2},已上传{self.cpcount}行')

class BtfyPtFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'当前未导入翻译表')
        self.createWidgets()
        Thread(target=self.getkwcount).start()

    def createWidgets(self):
        Label(self, text='标题翻译 - 葡萄牙语', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        Label(f1, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01,text='模版类型:').pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.MbtypeSelect['value']=list(MBTYPES.keys())
        self.MbtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect.current(0)
        self.MbtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        Label(f01,text='货币类型:').pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.HBtypeSelect['value']=CURRENCYS
        self.HBtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect.current(0)
        self.HBtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)
        Label(f00, text='最大字符数:').pack(side=LEFT, padx=2, pady=2)
        self.WordNumIpt = Entry(f00,width=10)
        self.WordNumIpt.insert(0,4500)
        self.WordNumIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00,text='导出个数:').pack(side=LEFT, padx=2, pady=2)
        self.fileNumIpt = Entry(f00,width=10)
        self.fileNumIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_dc=Button(f00,text='导出原始标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_title).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,padx=2)
        self.btn_dr=Button(f02,text='上传葡萄牙标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yntitle).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        time.sleep(20)
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        self.statustxt.set('正在获取数据...')
        try:
            rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where datatype=? and currency=? and pon=1''',(datatype,currency))
            
            rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where PtTitle is not null 
                                  and datatype=? and currency=? and pon=1''',(datatype,currency))
            
            self.statustxt.set(f'模版类型:{mbtypename},货币类型:{currency},需翻译标题共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
        except Exception as e:
            pass


    def dc_title(self):
        
        if not self.WordNumIpt.get() or not self.fileNumIpt.get():
            messagebox.showinfo('提示','请先填写字符限制和文件数量')
            return
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        self.btn_dc.config(state=DISABLED)
        wordnum=int(self.WordNumIpt.get())
        filenum=int(self.fileNumIpt.get())
        print(f'每份文件最大字符数:{wordnum},导出文件个数:{filenum}')
        rp=self.ms.ExecQuery(f'''select itemid,pname from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                    pon=1 and PtTitle is null and datatype=? and currency=? GROUP BY itemid) ''',(datatype,currency))
        current_timestamp = time.time()
        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(current_timestamp))
        dcmr=f'原始标题{current_time_str}'
        os.mkdir(dcmr)
        wordcount=0
        filecount=0
        row=0
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['PID','标题','葡萄牙标题'])
        for itemid,title in rp:
            try:
                if filecount>=filenum:
                    print(f'已经成功导出{filecount}份文件')
                    break
                if (wordcount+len(title))>=wordnum:
                    wb.save(f'{dcmr}/原始标题{filecount+1}.xlsx')
                    print(f'已导出{filecount+1}.xlsx')
                    wb=openpyxl.Workbook()
                    ws=wb[wb.sheetnames[0]]
                    ws.append(['PID','标题','葡萄牙标题'])
                    wordcount=0
                    filecount+=1
                    
                ws.append([itemid,title,''])
                wordcount+=len(title)
            except Exception as e:
                print(f'第{row}行,出错=>{e}')
            row+=1

        print(f'本次导出葡萄牙标题完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出葡萄牙标题完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yntitle(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)

        wb = openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池

            for item in tsb:
                executor.submit(self.uptask, [item['PID'],item['葡萄牙标题']])
        executor.shutdown(wait=True)
        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        #先生成随机码
        itemid,yntitle=item
        
        while True:
            sjm=get_random_code()
            rs= self.ms.ExecQuery('select count(*) from RandomCodes where RandomCode=?',(sjm,))
            if rs[0][0]==0:
                break
            time.sleep(1)
        yntitleRcode=f'{yntitle} |{sjm}|'
        aff=self.ms.ExecNoQuery('update ProShopeePh set pttitle=?,pttitleRcode=? where itemid=?',(yntitle,yntitleRcode,itemid))
        aff2=self.ms.ExecNoQuery('insert into RandomCodes(itemid,RandomCode) values(?,?)',(itemid,sjm))
        aff3=self.ms.ExecNoQuery('update proshopeeph_pmbid set hasPTtitle=1 where itemid=?',(itemid,))

        if aff>0:
            self.cpcount+=1
            print(f'成功翻译{itemid},随机码:{sjm},状态:{aff2},已上传{self.cpcount}行')

class BtfyThFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)

        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'当前未导入翻译表')
        self.createWidgets()
        Thread(target=self.getkwcount).start()

    def createWidgets(self):
        Label(self, text='标题翻译 - 泰语', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        Label(f1, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01,text='模版类型:').pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.MbtypeSelect['value']=list(MBTYPES.keys())
        self.MbtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.MbtypeSelect.current(0)
        self.MbtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        Label(f01,text='货币类型:').pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect = ttk.Combobox(f01,width=8,state='readonly')
        self.HBtypeSelect['value']=CURRENCYS
        self.HBtypeSelect.pack(side=LEFT, padx=2, pady=2)
        self.HBtypeSelect.current(0)
        self.HBtypeSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.getkwcount).start())

        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)
        Label(f00, text='最大字符数:').pack(side=LEFT, padx=2, pady=2)
        self.WordNumIpt = Entry(f00,width=10)
        self.WordNumIpt.insert(0,4500)
        self.WordNumIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00,text='导出个数:').pack(side=LEFT, padx=2, pady=2)
        self.fileNumIpt = Entry(f00,width=10)
        self.fileNumIpt.pack(side=LEFT, padx=2, pady=2)
        self.btn_dc=Button(f00,text='导出原始标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_title).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=2,fill=X,padx=2)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,padx=2)
        self.btn_dr=Button(f02,text='上传泰语标题',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yntitle).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        time.sleep(20)
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        self.statustxt.set('正在获取数据...')
        try:
            rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where 
                                  datatype=? and currency=? and pon=1''',(datatype,currency))
            
            rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where thTitle is not null 
                                  and datatype=? and currency=? and pon=1''',(datatype,currency))
            
            self.statustxt.set(f'模版类型:{mbtypename},货币类型:{currency},需翻译标题共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
        except Exception as e:
            pass


    def dc_title(self):
        
        if not self.WordNumIpt.get() or not self.fileNumIpt.get():
            messagebox.showinfo('提示','请先填写字符限制和文件数量')
            return
        mbtypename=self.MbtypeSelect.get()
        datatype=MBTYPES[mbtypename]
        currency=self.HBtypeSelect.get()
        self.btn_dc.config(state=DISABLED)
        wordnum=int(self.WordNumIpt.get())
        filenum=int(self.fileNumIpt.get())
        print(f'每份文件最大字符数:{wordnum},导出文件个数:{filenum}')
        rp=self.ms.ExecQuery(f'''select itemid,pname from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                pon=1 and thTitle is null and datatype=? and currency=? GROUP BY itemid) ''',(datatype,currency))
        current_timestamp = time.time()
        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(current_timestamp))
        dcmr=f'原始标题{current_time_str}'
        os.mkdir(dcmr)
        wordcount=0
        filecount=0
        row=0
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['PID','标题','泰语标题'])
        for itemid,title in rp:
            try:
                if filecount>=filenum:
                    print(f'已经成功导出{filecount}份文件')
                    break
                if (wordcount+len(title))>=wordnum:
                    wb.save(f'{dcmr}/原始标题{filecount+1}.xlsx')
                    print(f'已导出{filecount+1}.xlsx')
                    wb=openpyxl.Workbook()
                    ws=wb[wb.sheetnames[0]]
                    ws.append(['PID','标题','泰语标题'])
                    wordcount=0
                    filecount+=1
                    
                ws.append([itemid,title,''])
                wordcount+=len(title)
            except Exception as e:
                print(f'第{row}行,出错=>{e}')
            row+=1

        print(f'本次导出泰语标题完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出泰语标题完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yntitle(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)

        wb = openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池

            for item in tsb:
                executor.submit(self.uptask, [item['PID'],item['泰语标题']])
        executor.shutdown(wait=True)
        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        #先生成随机码
        itemid,yntitle=item
        
        while True:
            sjm=get_random_code()
            rs= self.ms.ExecQuery('select count(*) from RandomCodes where RandomCode=?',(sjm,))
            if rs[0][0]==0:
                break
            time.sleep(1)
        yntitleRcode=f'{yntitle} |{sjm}|'
        aff=self.ms.ExecNoQuery('update ProShopeePh set thTitle=?,thTitleRcode=? where itemid=?',(yntitle,yntitleRcode,itemid))
        aff2=self.ms.ExecNoQuery('insert into RandomCodes(itemid,RandomCode) values(?,?)',(itemid,sjm))
        aff3=self.ms.ExecNoQuery('update proshopeeph_pmbid set hasThtitle=1 where itemid=?',(itemid,))
        
        if aff>0:
            self.cpcount+=1
            print(f'成功翻译{itemid},随机码:{sjm},状态:{aff2},已上传{self.cpcount}行')


# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x750")
# list_win.title('数据管理')
# BTFYFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()